/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import DTO.*;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author QuangHau
 */
public class TinTucDAO {

    //danh sach tin tuc
    public static ArrayList<TinTucDTO> SelectDanhSachTinTuc(int maWebsite, int maChuDe) {
        Connection conn = DataProvider.Connection();
        if (conn != null) {
            try {
                CallableStatement cs = conn.prepareCall("{call usp_SelectDanhSachTinTuc(?,?)}");
                if (maWebsite != -1) {
                    cs.setInt("mawebsite", maWebsite);
                } else {
                    cs.setObject("mawebsite", null);
                }

                if (maChuDe != -1) {
                    cs.setInt("machude", maChuDe);
                } else {
                    cs.setObject("machude", null);
                }

                ResultSet rs = cs.executeQuery();
                ArrayList<TinTucDTO> arr = new ArrayList<TinTucDTO>();
                while (rs.next()) {
                    TinTucDTO item = new TinTucDTO();
                    item.setMaTinTuc(rs.getInt("matintuc"));
                    item.setLink(rs.getString("link"));
                    item.setTieuDe(rs.getString("tieude"));
                    item.setHinhAnh(rs.getString("hinhanh"));
                    item.setNgayDang(rs.getTimestamp("ngaydang"));
                    item.setMoTa(rs.getString("mota"));
                    item.setMaChuDe(rs.getInt("machude"));

                    item.setTenWebsite(rs.getString("tenwebsite"));
                    item.setTenChuDe(rs.getString("tenchude"));
                    item.setLinkChuDe(rs.getString("linkchude"));
                    arr.add(item);
                }
                conn.close();
                return arr;

            } catch (SQLException ex) {
                Logger.getLogger(TinTucDAO.class.getName()).log(Level.SEVERE, null, ex);
                return null;
            }
        }
        return null;
    }

    public static ArrayList<TinTucDTO> SelectDanhSachTinTuc_ChuDe(int maChuDe) {
        Connection conn = DataProvider.Connection();
        if (conn != null) {
            try {
                CallableStatement cs = conn.prepareCall("{call usp_SelectDanhSachTinTuc(?,?)}");
                cs.setObject("mawebsite", null);
                cs.setInt("machude", maChuDe);
                ResultSet rs = cs.executeQuery();
                ArrayList<TinTucDTO> arr = new ArrayList<TinTucDTO>();
                while (rs.next()) {
                    TinTucDTO item = new TinTucDTO();
                    item.setMaTinTuc(rs.getInt("matintuc"));
                    item.setLink(rs.getString("link"));
                    item.setTieuDe(rs.getString("tieude"));
                    item.setHinhAnh(rs.getString("hinhanh"));
                    item.setNgayDang(rs.getTimestamp("ngaydang"));
                    item.setMoTa(rs.getString("mota"));
                    item.setMaChuDe(rs.getInt("machude"));

                    item.setTenWebsite(rs.getString("tenwebsite"));
                    item.setTenChuDe(rs.getString("tenchude"));
                    item.setLinkChuDe(rs.getString("linkchude"));
                    arr.add(item);
                }
                conn.close();
                return arr;

            } catch (SQLException ex) {
                Logger.getLogger(TinTucDAO.class.getName()).log(Level.SEVERE, null, ex);
                return null;
            }
        }
        return null;
    }

    public static ArrayList<TinTucDTO> SelectDanhSachTinTuc_All() {
        Connection conn = DataProvider.Connection();
        if (conn != null) {
            try {
                CallableStatement cs = conn.prepareCall("{call usp_SelectDanhSachTinTuc}");
                ResultSet rs = cs.executeQuery();
                System.out.println(rs);
                ArrayList<TinTucDTO> arr = new ArrayList<TinTucDTO>();
                while (rs.next()) {
                    TinTucDTO item = new TinTucDTO();
                    item.setMaTinTuc(rs.getInt("matintuc"));
                    item.setLink(rs.getString("link"));
                    item.setTieuDe(rs.getString("tieude"));
                    item.setHinhAnh(rs.getString("hinhanh"));
                    item.setNgayDang(rs.getTimestamp("ngaydang"));
                    item.setMoTa(rs.getString("mota"));
                    item.setMaChuDe(rs.getInt("machude"));

                    item.setTenWebsite(rs.getString("tenwebsite"));
                    item.setTenChuDe(rs.getString("tenchude"));
                    item.setLinkChuDe(rs.getString("linkchude"));
                    arr.add(item);
                }

                conn.close();
                return arr;

            } catch (SQLException ex) {
                Logger.getLogger(TinTucDAO.class.getName()).log(Level.SEVERE, null, ex);
                return null;
            }
        }
        return null;
    }

    public static ArrayList<TinTucDTO> SelectDanhSachTinTuc_Website(int maWebsite) {
        Connection conn = DataProvider.Connection();
        if (conn != null) {
            try {
                CallableStatement cs = conn.prepareCall("{call usp_SelectDanhSachTinTuc(?,?)}");
                cs.setInt("mawebsite", maWebsite);
                cs.setObject("machude", null);
                ResultSet rs = cs.executeQuery();
                ArrayList<TinTucDTO> arr = new ArrayList<TinTucDTO>();
                while (rs.next()) {
                    TinTucDTO item = new TinTucDTO();
                    item.setMaTinTuc(rs.getInt("matintuc"));
                    item.setLink(rs.getString("link"));
                    item.setTieuDe(rs.getString("tieude"));
                    item.setHinhAnh(rs.getString("hinhanh"));
                    item.setNgayDang(rs.getTimestamp("ngaydang"));
                    item.setMoTa(rs.getString("mota"));
                    item.setMaChuDe(rs.getInt("machude"));

                    item.setTenWebsite(rs.getString("tenwebsite"));
                    item.setTenChuDe(rs.getString("tenchude"));
                    item.setLinkChuDe(rs.getString("linkchude"));
                    arr.add(item);
                }
                conn.close();
                return arr;

            } catch (SQLException ex) {
                Logger.getLogger(TinTucDAO.class.getName()).log(Level.SEVERE, null, ex);
                return null;
            }
        }
        return null;
    }
//******************

    public static TinTucDTO SelectTinTuc(int matintuc) {
        Connection conn = DataProvider.Connection();
        if (conn != null) {
            try {
                CallableStatement cs = conn.prepareCall("{call usp_SelectTinTuc_ID(?)}");
                cs.setInt("matintuc", matintuc);

                ResultSet rs = cs.executeQuery();
                TinTucDTO tintucDTO = null;
                if (rs.next()) {
                    tintucDTO = new TinTucDTO();
                    tintucDTO.setMaTinTuc(rs.getInt("matintuc"));
                    tintucDTO.setLink(rs.getString("link"));
                    tintucDTO.setTieuDe(rs.getString("tieude"));
                    tintucDTO.setHinhAnh(rs.getString("hinhanh"));
                    tintucDTO.setNgayDang(rs.getTimestamp("ngaydang"));
                    tintucDTO.setMoTa(rs.getString("mota"));
                    tintucDTO.setMaChuDe(rs.getInt("machude"));

                    tintucDTO.setTenWebsite(rs.getString("tenwebsite"));
                    tintucDTO.setTenChuDe(rs.getString("tenchude"));
                    tintucDTO.setLinkChuDe(rs.getString("linkchude"));
                }
                conn.close();
                return tintucDTO;

            } catch (SQLException ex) {
                Logger.getLogger(TinTucDAO.class.getName()).log(Level.SEVERE, null, ex);
                return null;
            }
        }
        return null;
    }

    public static boolean InsertTinTuc(TinTucDTO tintucDTO) {
        //    @link varchar(225) , @tieude text,
//    @hinhanh nvarchar(50), @ngaydang datetime,
//    @mota text, @machude int
        Connection conn = DataProvider.Connection();
        if (conn != null) {
            try {
                CallableStatement cs = conn.prepareCall("{call usp_InsertTinTuc(?,?,?,?,?,?)}");
                cs.setString("link", tintucDTO.getLink());
                cs.setString("tieude", tintucDTO.getTieuDe());
                cs.setString("hinhanh", tintucDTO.getHinhAnh());
                cs.setTimestamp("ngaydang", new java.sql.Timestamp(tintucDTO.getNgayDang().getTime()));
                cs.setString("mota", tintucDTO.getMoTa());
                cs.setInt("machude", tintucDTO.getMaChuDe());
                int result = cs.executeUpdate();
                conn.close();
                if (result > 0) {
                    return true;
                }
                return false;
            } catch (SQLException ex) {
                Logger.getLogger(TinTucDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return false;
    }

    public static boolean DeleteTinTuc(int matintuc) {
        //@matintuc int
        Connection conn = DataProvider.Connection();
        if (conn != null) {
            try {
                CallableStatement cs = conn.prepareCall("{call usp_DeleteTinTuc(?)}");
                cs.setInt("matintuc", matintuc);
                int result = cs.executeUpdate();
                conn.close();
                if (result > 0) {
                    return true;
                }
                return false;
            } catch (SQLException ex) {
                Logger.getLogger(TinTucDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return false;
    }

    public static boolean checkTinTuc(String  tieuDe, int machuDe) {
        Connection con = DataProvider.Connection();
        if (con != null) {
            try {
                PreparedStatement ps;
                ps = con.prepareCall("select * from tintuc where tieude like ? and machude = ?");
                ps.setString(1, tieuDe);
                ps.setInt(2, machuDe);
                ResultSet rs = ps.executeQuery();
                if (rs.next()) {
                    return true;
                }
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(ChuDeDAO.class.getName()).log(Level.SEVERE, null, ex);
                return false;
            }
        }
        return false;
    }
    public static int maxId() {
        Connection con = DataProvider.Connection();
        if (con != null) {
            try {
                PreparedStatement ps;
                ps = con.prepareCall("select max(matintuc) as maxid from tintuc");
                ResultSet rs = ps.executeQuery();
                if (rs.next()) {
                    return rs.getInt("maxid");
                }
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(ChuDeDAO.class.getName()).log(Level.SEVERE, null, ex);
                return -1;
            }
        }
        return -1;
    }
}
